import py_mysql_util

"""
zhangsan,zs00123,张先生,张三,17719283819,上海
zhanglaoer,zle00123,二爷,张老二,177192833819,山东
wangjianguo,zjg00123,专业谐音梗,王建国,19284338219,上海
xiaohuanxiong,xiaohx29@,呼兰,呼兰,17719283344,上海
lidan,lidan(,李诞,李诞,18208393201,山东
lixueqing,lxqyy993*,李MM,李雪琴,,山东
jianing,jianing28(,佳宁,佳宁,18200839201,山东
lidan,lidan(,李诞,李诞,18208393201,山东
"""


class UserInfo:

    def __init__(self, uname, upwd, nick_name, real_name, mobile, city) -> None:
        self.uname = uname
        self.upwd = upwd
        self.nick_name = nick_name
        self.real_name = real_name
        self.mobile = mobile
        self.city = city
        super().__init__()

    def __eq__(self, o: object) -> bool:
        if self.uname == o.uname and self.upwd == o.upwd and self.nick_name == o.nick_name \
                and self.real_name == o.real_name and self.mobile == o.mobile and self.city == o.city:
            return True
        return False

    def __hash__(self) -> int:
        return hash(self.mobile)


# 读取用户文件内容
def read_user_info():
    with open("userinfo.txt", "r", encoding="utf-8") as text:
        temp_list = text.readlines()
        # print(temp_list)
        # 移除第一行文字描述内容
        del temp_list[0]
        return temp_list


# 将数据组装到对象集合中并去重
def make_user_data(temp_list: list):
    user_set = set()
    for usertext in temp_list:
        if usertext == '\n':
            continue
        user = usertext.split(",")
        # 过滤掉不符合条件的用户
        if not (user[3] == '' or user[4] == '' or len(user[4]) != 11):
            # print(user)
            temp_user = UserInfo(user[0], user[1], user[2], user[3], user[4], user[5])
            user_set.add(temp_user)
    return user_set

# 将数据保存到数据库
def add_db(user_set: set):
    fmt_col=lambda s1:"'" + s1 + "',"
    fmt_col_end=lambda s1:"'" + s1 + "'"
    sql_prefix = "INSERT INTO `pytest`.`user_info`(`uname`, `upwd`, `nick_name`, `real_name`, `mobile`, `city`) VALUES "
    sql_body = ""
    # 组装sql,多条数据一次性插入
    for user in user_set:
        sql_body = sql_body + "("+fmt_col(user.uname) + fmt_col(user.upwd) + fmt_col(user.nick_name) + fmt_col(
            user.real_name) + fmt_col(user.mobile) + fmt_col_end(user.city)+"),"
    sql_body=sql_body[0:len(sql_body)-1]
    sql_str=sql_prefix+sql_body
    # print(sql_str)

    py_mysql_util.insert_db(sql_str)

# 查询表所有结果
def find_all_user():
    sql="SELECT `id`, `uname`, `upwd`, `nick_name`, `real_name`, `mobile`, `city`,`create_time` FROM user_info"
    user_list=py_mysql_util.find_list(sql)
    print("查询结果:",user_list)



if __name__ == '__main__':
    # 读取文件数据
    user_text = read_user_info()
    # 整理数据
    user_set = make_user_data(user_text)
    # print(f'user_list = {len(user_set)}')
    # 保存数据到数据库
    # add_db(user_set)
    # 查询所有数据
    find_all_user()
